<?php
/**
 *
 * @author Strawberry🍓
 * @date 2021-05-21 4:08 下午
 *
 */

namespace app\common\model\tang;


use app\common\impl\Comm;
use app\common\model\CommonBaseModel;
use app\common\model\tang\Cost;
use Jrk\Tree;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;


class CostRule extends CommonBaseModel implements Comm
{

    public function getAdminPageData($param = [], $order = 'id asc',$where =[] , $nickname = '')
    {
        $model = (new Cost())->where($where);
        if (isset($param['merchant_id']) && $param['merchant_id']){
            $model->where('merchant_id','=',$param['merchant_id']);

        }
//        if (isset($param['mobile']) && $param['mobile']){
//            $model->where('mobile','like',"%{$param['mobile']}%");
//        }
//        if (isset($param['cost_type_id']) && $param['cost_type_id']){
//            $model->where('cost_type_id','=',$param['cost_type_id']);
//
//        }
//        if (isset($param['order_sn']) && $param['order_sn']){
//            $model->where('order_sn','like',"%{$param['order_sn']}%");
////            var_dump($param['order_sn']);die;
//        }

        if (!empty($param['Date'])){
            if(!empty($param['Date']['startDate'])){
                $model->where('pay_time','>=',$param['Date']['startDate']) ;
            }
            if(!empty($param['Date']['endDate'])){
                $model->where('pay_time','<=',$param['Date']['endDate']);
            }
        }
        if(!empty($param['export'])){
            if(!empty($param['startDate'])){
                $model->where('pay_time','>=',$param['startDate']) ;
            }
            if(!empty($param['endDate'])){
                $model->where('pay_time','<=',$param['endDate']);
            }
            $data = $model->with(['merchants','costRules','payType','costType','subjectList'])->order($order)->select()->toArray();
        }else{
            $data = $model->with(['merchants','costRules','payType','costType','subjectList'])->order($order)->page(PAGE)->limit(LIMIT)->select()->toArray();
        }



        $count = $model->count("id");
        $datas = [] ;
        if(!empty($param['export'])) {
            $hd = [
                'company'  =>  '公司',
                'jz_time'  =>  '记账日期',
                'yw_time'  =>  '业务日期', // +
                'remarkVal'=>  '会计期间',
                'pingztype'=>   '凭证类型',
                'pingz'    =>   '凭证号',
                'fenlu'    =>   '分录号',
                'zaiyao'   =>  '摘要',
                'cost_sn'  =>  '科目',
                'BZ'       =>  '币种',
                'HL'       =>   '汇率',
                'FX'       =>  '方向',
                'money'    =>  '原币金额',
                'SL'       =>   '数量',
                'DJ'       =>  '单价',
                'j_money'  =>  '借方金额',
                'd_money'  =>  '贷方金额',
                'admin'    =>  '制单人',
                'GZR'      =>  '过账人',
                'SHR'      =>   '审核人',
                'FJSL'     =>   '附件数量',
                'GZBJ'     =>  '过账标记',
                'JZPZMK'   =>   '机制凭证模块',
                'SCBJ'     =>  '删除标记',
                'PZXH'     =>   '凭证序号',
                'DW'       =>  '单位',
                'CKXX'     =>   '参考信息',
                'SFXJLL'   =>  '是否有现金流量',
                'XJLLBJ'   =>   '现金流量标记',
                'YWBH'     =>  '业务编号',
                'JSFS'     =>   '结算方式',
                'JSH'      =>  '结算号',
                'FZZZY'    =>   '辅助账摘要',
                'xm1'      =>  '核算项目1',
                'sn1'      =>  '编码1',
                'name1'    =>  '名称1',
                'xm2'      =>   '核算项目2',
                'sn2'      =>  '编码2',
                'name2'    =>   '名称2',
                'xm3'      =>   '核算项目3',
                'sn3'      =>  '编码3',
                'name3'    =>   '名称3',
                'xm4'      =>   '核算项目4',
                'sn4'      =>  '编码4',
                'name4'    =>   '名称4',
                'xm5'      =>   '核算项目5',
                'sn5'      =>  '编码5',
                'name5'    =>   '名称5',
                'xm6'      =>   '核算项目6',
                'sn6'      =>  '编码6',
                'name6'    =>   '名称6',
                'xm7'      =>   '核算项目7',
                'sn7'      =>  '编码7',
                'name7'    =>   '名称7',
                'xm8'      =>   '核算项目8',
                'sn8'      =>  '编码8',
                'name8'    =>   '名称8',
                'FPH'      =>  '发票号',
                'HPPZ'     =>   '换票证号',
                'KH'       =>   '客户',
                'FYLB'     =>  '费用类别',
                'SKR'      =>   '收款人',
                'WL'       =>   '物料',
                'CWZZ'     =>  '财务组织',
                'GYS'      =>   '供应商',
                'FZZYWRQ'  =>   '辅助账业务日期',
                'DQR'      =>  '到期日',

//                'carry_down_sn'    =>  '结转收入对应的科目',
//                'carry_down_name'  =>  '科目名称',
//                'tax_rate'         =>   '税率',
//                'tax_sn'           =>  '税金科目',
//                'tax_name'         =>   '科目名称',
            ];
//            $datas[0] = $hd;
        }
        if($data){
            foreach ($data as $v){
                 if($v['costRules']){
                     foreach ($v['costRules'] as $v1){
                         $zaiyao = [] ;
                         $str= date('m月d日',strtotime($v['pay_time'])).'总收收取' . $v['merchants']['username'] . '('.$v['merchants']['merchant_sn'].')' ;
                         switch ($v['type']){
                             case  1:   // 押金
                                 $str .= $v1['name'];
                                 if($v1['name'] == '手续费'){
                                     $cost_sn = $v['payType']['subject_sn'];
                                     $zaiyao[] = [
                                         'str'  => $str,
                                         'cost_sn' => $cost_sn
                                     ] ;
                                 }else{
                                     $zaiyao[] = [
                                         'str'  => $str,
                                     ] ;
                                 }
                                 break ;
                             case  2:   // 费用
                                 if($v1['is_carry_down']==1){   // 转结存订单
                                     if($v1['carry_down_type']==1){
                                         if($v1['tax_money']>0){
                                             $str1 = $str . $v1['name'];
                                             $str2 = $str . $v1['name'] . '收入';
                                             $str3 = $str . $v1['name'].'的销项税';
                                             if(!empty($v['b_start_time'])){
                                                 $str1 .= '(' .date('Y.m.d',strtotime($v['b_start_time'])) . ' - ' ;
                                                 $str2 .= '(' .date('Y.m.d',strtotime($v['b_start_time'])) . ' - ' ;
                                             }
                                             if(!empty($v['b_start_time'])){
                                                 $str1 .= date('Y.m.d',strtotime($v['b_end_time'])). ')' ;
                                                 $str2 .= date('Y.m.d',strtotime($v['b_end_time'])). ')' ;
                                             }else{
                                                 if(!empty($v['b_start_time'])){
                                                     $str1 .=   ')' ;
                                                 }
                                             }
                                             $zaiyao[0]['str'] = $str1 ;
                                             $zaiyao[1]['str'] = $str2 ;
                                             $zaiyao[2]['str'] = $str3 ;
                                             $zaiyao[0]['FZZZY'] = $str1 ;
                                             $zaiyao[1]['FZZZY'] = $str2 ;
                                         }else{
                                             $str .= $v1['name'];
                                             $zaiyao[]['str'] = $str ;
                                         }
                                     }else{
                                         $str .= $v1['name'];
                                         $zaiyao[]['str'] = $str ;
                                     }
                                 }else{
                                     if($v1['tax_money']>0){
                                         $str1 = $str . $v1['name'];
                                         $str2 = $str . $v1['name'].'的销项税金';
                                         if(!empty($v['b_start_time'])){
                                             $str1 .= '(' .date('Y.m.d',strtotime($v['b_start_time'])) . ' - ' ;
                                             $str2 .= '(' .date('Y.m.d',strtotime($v['b_start_time'])) . ' - ' ;
                                         }
                                         if(!empty($v['b_end_time'])){
                                             $str1 .= date('Y.m.d',strtotime($v['b_end_time'])). ')' ;
                                             $str2 .= date('Y.m.d',strtotime($v['b_end_time'])). ')' ;
                                         }else{
                                             if(!empty($v['b_start_time'])){
                                                 $str1 .=   ')' ;
                                                 $str2 .=   ')' ;
                                             }
                                         }
                                         $zaiyao[0]['str'] = $str1 ;
                                         $zaiyao[1]['str'] = $str2 ;
                                         $zaiyao[0]['FZZZY'] = $str1 ;
                                     }else{
                                         $str .= $v1['name'];
                                         if($v1['name'] == '手续费'){
                                             $cost_sn = $v['payType']['subject_sn'];
                                             $zaiyao[] = [
                                                 'str'  => $str,
                                                 'cost_sn' => $cost_sn
                                             ] ;
                                         }else{
                                             $zaiyao[] = [
                                                 'str'  => $str,
                                             ] ;
                                         }
//                                         $str .= $v1['name'];
//                                         $zaiyao[] = $str ;
                                     }
                                 }
                                 break ;
                             case  3:  // 证件
                                 if($v1['tax_money']>0){
                                     $str1 = $str . $v1['name'];
                                     $str2 = $str . $v1['name'].'的销项税金';
                                     $zaiyao[0]['str'] = $str1 ;
                                     $zaiyao[1]['str'] = $str2 ;
                                 }else{
                                     $str .= $v1['name'];
                                     if($v1['name'] == '手续费'){
                                         $cost_sn = $v['payType']['subject_sn'];
                                         $zaiyao[] = [
                                             'str'  => $str,
                                             'cost_sn' => $cost_sn
                                         ] ;
                                     }else{
                                         $zaiyao[] = [
                                             'str'  => $str,
                                         ] ;
                                     }
//                                     $zaiyao[] = $str ;
                                 }
                                 break ;
                         }
                         $sum = count($zaiyao) ;
                         foreach ($zaiyao as $k => $v3){
                             if(!empty($v3['FZZZY'])){
                                 $FZZZY = $v3['FZZZY'];
                             }else{
                                 $FZZZY = '';
                             }

//                             if($v['type']==2 && $v1['tax_money']<=0){
//                                 $FZZZY = '' ;
//                             }else{
//                                 $FZZZY = $v3['FZZZY'];
//                             }
                             if($sum==3){

                                 $j_money = 0 ;
                                 $d_money = 0 ;
                                 switch ($k){
                                     case 0 :
                                         $cost_sn = $v1['cost_sn'];
                                         $tax_money = $v1['z_money'] ;
                                         $j_money = $v1['z_money'] ;
                                         $d_money = 0 ;
                                         break ;
                                     case 1 :
                                         $cost_sn = $v1['costType']['carry_down_sn']  ;
                                         $tax_money = $v1['money'];
                                         $j_money = 0 ;
                                         $d_money = $v1['money'];
                                         break ;
                                     case 2 :
                                         $cost_sn = $v1['costType']['carry_down_sn']  ;
                                         $tax_money = $v1['tax_money'] ;
                                         $j_money = 0 ;
                                         $d_money = $v1['tax_money'] ;
                                         break ;
                                 }
                                 if(!empty($v3['cost_sn'])){
                                     $cost_sn = $v3['cost_sn'] ;
                                 }
                                 $arr = [
                                     'company'  =>  'JX0504',
                                     'jz_time'  =>  date('Y/m/d',strtotime($v['pay_time'])),
                                     'yw_time'  =>  date('Y/m/d',strtotime($v['pay_time'])),
                                     'remarkVal'=>  empty($param['remarkVal']) ? '': $param['remarkVal'],
                                     'pingztype'=>  '记',
                                     'pingz'    =>  '记0082',
                                     'fenlu'    =>  $v1['id'],
                                     'zaiyao'   =>  $v3['str'] ,
                                     'cost_sn'  =>  $cost_sn,
                                     'BZ'       =>  'BB01',
                                     'HL'       =>  '1',
                                     'FX'       =>  '0',
                                     'money'    =>  $tax_money,
                                     'SL'       =>  $v['number'],
                                     'DJ'       =>  $v1['unitprice'],
                                     'j_money'  =>  $j_money,
                                     'd_money'  =>  $d_money,
                                     'admin'    =>  $nickname,
                                     'GZR'      =>  '',
                                     'SHR'      =>  '',
                                     'FJSL'     =>  '',
                                     'GZBJ'     =>  '',
                                     'JZPZMK'   =>  '',
                                     'SCBJ'     =>  '',
                                     'PZXH'     =>  '',
                                     'DW'       =>  '',
                                     'CKXX'     =>  '',
                                     'SFXJLL'   =>  '',
                                     'XJLLBJ'   =>  '',
                                     'YWBH'     =>  '',
                                     'JSFS'     =>  '',
                                     'JSH'      =>  '',
                                     'FZZZY'    =>  $FZZZY
                                 ];
                             }else{
                                 if($k==1){
                                     if(!empty($v3['cost_sn'])){
                                         $cost_sn = $v3['cost_sn'] ;
                                     }else{
                                         $cost_sn = $v1['tax_sn'] ;
                                     }
                                     $arr = [
                                         'company'  =>  'JX0504',
                                         'jz_time'  =>  date('Y/m/d',strtotime($v['pay_time'])),
                                         'yw_time'  =>  date('Y/m/d',strtotime($v['pay_time'])),
                                         'remarkVal'=>  empty($param['remarkVal']) ? '': $param['remarkVal'],
                                         'pingztype'=>  '记',
                                         'pingz'    =>  '记0082',
                                         'fenlu'    =>  $v1['id'],
                                         'zaiyao'   =>  $v3['str'] ,
                                         'cost_sn'  =>  $cost_sn,
                                         'BZ'       =>  'BB01',
                                         'HL'       =>  '1',
                                         'FX'       =>  '0',
                                         'money'    =>  $v1['tax_money'],
                                         'SL'       =>  $v['number'],
                                         'DJ'       =>  $v1['unitprice'],
                                         'j_money'  =>  0,
                                         'd_money'  =>  $v1['tax_money'],
                                         'admin'    =>  $nickname,
                                         'GZR'      =>  '',
                                         'SHR'      =>  '',
                                         'FJSL'     =>  '',
                                         'GZBJ'     =>  '',
                                         'JZPZMK'   =>  '',
                                         'SCBJ'     =>  '',
                                         'PZXH'     =>  '',
                                         'DW'       =>  '',
                                         'CKXX'     =>  '',
                                         'SFXJLL'   =>  '',
                                         'XJLLBJ'   =>  '',
                                         'YWBH'     =>  '',
                                         'JSFS'     =>  '',
                                         'JSH'      =>  '',
                                         'FZZZY'    =>  $FZZZY
                                     ];
                                 }else{
                                     if(!empty($v3['cost_sn'])){
                                         $cost_sn = $v3['cost_sn'] ;
                                     }else{
                                         $cost_sn = $v1['cost_sn'] ;
                                     }
                                     $arr = [
                                         'company'  =>  'JX0504',
                                         'jz_time'  =>  date('Y/m/d',strtotime($v['pay_time'])),
                                         'yw_time'  =>  date('Y/m/d',strtotime($v['pay_time'])),
                                         'remarkVal'=>  empty($param['remarkVal']) ? '': $param['remarkVal'],
                                         'pingztype'=>  '记',
                                         'pingz'    =>  '记0082',
                                         'fenlu'    =>  $v1['id'],
                                         'zaiyao'   =>  $v3['str'] ,
                                         'cost_sn'  =>  $cost_sn,
                                         'BZ'       =>  'BB01',
                                         'HL'       =>  '1',
                                         'FX'       =>  '0',
                                         'money'    =>  $v1['money'],
                                         'SL'       =>  $v['number'],
                                         'DJ'       =>  $v1['unitprice'],
                                         'j_money'  =>  0,
                                         'd_money'  =>  $v1['money'],
                                         'admin'    =>  $nickname,
                                         'GZR'      =>  '',
                                         'SHR'      =>  '',
                                         'FJSL'     =>  '',
                                         'GZBJ'     =>  '',
                                         'JZPZMK'   =>  '',
                                         'SCBJ'     =>  '',
                                         'PZXH'     =>  '',
                                         'DW'       =>  '',
                                         'CKXX'     =>  '',
                                         'SFXJLL'   =>  '',
                                         'XJLLBJ'   =>  '',
                                         'YWBH'     =>  '',
                                         'JSFS'     =>  '',
                                         'JSH'      =>  '',
                                         'FZZZY'    =>  $FZZZY
                                     ];
                                 }
                             }

                             $arr['xm1']   = '' ;
                             $arr['sn1']   = '';
                             $arr['name1'] =  '';
                             $arr['xm2']   = '' ;
                             $arr['sn2']   = '';
                             $arr['name2'] =  '';
                             if(!empty($v1['costType']['auxiliary'])){
                                 $auxiliary = explode(',',$v1['costType']['auxiliary']);
                                 foreach ($auxiliary as $value){
                                     if($FZZZY){
                                         if($value==1){  //部门
                                             $arr['xm1'] = '部门' ;
                                             $arr['sn1'] = $v['subjectList']['subject_sn'];
                                             $arr['name1'] =  $v['subjectList']['name'];
                                         }else{
                                             $arr['xm2'] = '客户' ;
                                             $arr['sn2'] = $v['merchants']['merchant_sn'];
                                             $arr['name2'] =  $v['merchants']['username'];
                                         }
                                     }
                                 }
                             }
                             $arr['xm3']    = '' ;
                             $arr['sn3']    = '' ;
                             $arr['name3']  = '' ;
                             $arr['xm4']    = '' ;
                             $arr['sn4']    = '' ;
                             $arr['name4']  = '' ;
                             $arr['xm5']    = '' ;
                             $arr['sn5']    = '' ;
                             $arr['name5']  = '' ;
                             $arr['xm6']    = '' ;
                             $arr['sn6']    = '' ;
                             $arr['name6']  = '' ;
                             $arr['xm7']    = '' ;
                             $arr['sn7']    = '' ;
                             $arr['name7']  = '' ;
                             $arr['xm8']    = '' ;
                             $arr['sn8']    = '' ;
                             $arr['name8']  = '' ;
                             $arr['FPH']    = '' ;
                             $arr['HPPZ']   = '' ;
                             $arr['KH']     = '' ;
                             $arr['FYLB']   = '' ;
                             $arr['SKR']    = '' ;
                             $arr['WL']     = '' ;
                             $arr['CWZZ']   = '' ;
                             $arr['GYS']    = '' ;
                             $arr['FZZYWRQ']= '' ;
                             $arr['DQR']    = '' ;
                             $arr['carry_down_sn']    =  '';
                             $arr['carry_down_name']  =  '' ;
                             $arr['tax_rate']         =  '';
                             $arr['tax_sn']           =  '';
                             $arr['tax_name']         =  '';
                             if($v1['is_carry_down']==1 && empty($param['export'])){
                                 $arr['carry_down_sn']    =  $v1['costType']['carry_down_sn'] ;
                                 $arr['carry_down_name']  =  $v1['costType']['carry_down_name'] ;
                                 $arr['tax_rate']         =  $v1['costType']['tax_rate'];
                                 $arr['tax_sn']           =  $v1['costType']['tax_sn'];
                                 $arr['tax_name']         =  $v1['costType']['tax_name'];
                             }
                             $datas[] =  $arr ;
                         }

                     }
                 }
            }
        }

        if(!empty($param['export'])) {
            $b1 = [
                '公司','记账日期','会计期间','凭证类型','凭证号','币种','分录号','对方分录号','主表信息','附表信息','原币','本位币','报告币','主表金额系数','附表金额系数','性质','核算项目1','编码1','名称1','核算项目2','编码2','名称2','核算项目3','编码3','名称3','核算项目4','编码4','名称4','核算项目5','编码5','名称5','核算项目6','编码6','名称6','核算项目7','编码7','名称7','核算项目8','编码8','名称8'
            ];
            $this->export($datas,$hd,$b1);

        }
        return parent::ajaxResult($datas, $count);
    }

    private static $cellKey = array(
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
        'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
        'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM',
        'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ'
    );
    public function export($a,$a1,$b1){
        $name='财务报表'.date("Y-m-d H-i-s",time());
        $data['pz'] = $a ;
        $ak = [] ;
        $av = [] ;
        foreach ($a1 as $k => $v){
           $ak[] = $k;
           $av[] = $v;
        }
        //设置表头：
        $head['pz'] = $av;
        //数据中对应的字段，用于读取相应数据：
        $keys['pz'] = $ak;
        $count['pz'] = count($head['pz']);  //计算表头数量
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet()->setTitle('凭证');
        for ($i = 0; $i < $count['pz'] ; $i++) {
            $sheet->setCellValue(self::$cellKey[$i] . '1', $head['pz'][$i]);
        }

        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        foreach ($data['pz'] as $key => $item) {
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
            for ($i = 0; $i < $count['pz']; $i++) {     //数字转字母从65开始：
                $sheet->setCellValue(self::$cellKey[$i] . ($key + 2), ' '.$item[$keys['pz'][$i]]);
                $spreadsheet->getActiveSheet()->getColumnDimension(self::$cellKey[$i])->setWidth(20); //固定列宽
            }
        }
        $myWorkSheet['liul'] = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, '现金流量');//与下面的配合使用
        $spreadsheet->addSheet($myWorkSheet['liul'], 1);//将“My Data”工作表作为电子表格对象中的第一个工作表附加
        $head['liul'] = $b1;
        //数据中对应的字段，用于读取相应数据：

        $count['liul'] = count($head['liul']);  //计算表头数量
        for ($i = 0; $i < $count['liul']; $i++) {     //数字转字母从65开始，循环设置表头：
            $myWorkSheet['liul']->setCellValue(self::$cellKey[$i] . '1', $head['liul'][$i]);
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');

//        $writer = new Xlsx($spreadsheet);
//        $writer->save('php://output');
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');

        $objWriter->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    public function costType(): \think\model\relation\HasOne
    {
        return $this->hasOne(CostType::class,'id','cost_type_id');
    }

    public function delById($id)
    {
        // TODO: Implement delById() method.
    }

    public function doAll($data)
    {
        // TODO: Implement doAll() method.
        return parent::doAllData($data);
    }
}